package com.jp.fm.dao;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collections;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import net.sf.ehcache.Cache;
import net.sf.ehcache.Element;

import com.jp.fm.HomeFinanceRunner;
import com.jp.fm.data.BillReminder;

public class BillReminderDao {

	private static final String COLUMN_ACCOUNT = "ACCOUNT";
	private static final String COLUMN_AMOUNT = "AMOUNT";
	private static final String COLUMN_NAME = "NAME";
	private static final String COLUMN_DUE_DATE = "DUE_DATE";
	private static final String COLUMN_REPEAT = "REPEAT";

	public static final String CACHE_NAME = "BillReminder";

	public List<BillReminder> findByDueDateAndRange(final Integer range) {

		List<BillReminder> ret = new ArrayList<BillReminder>();
		List<BillReminder> allBrs = findAll();

		for (Iterator<BillReminder> iterator = allBrs.iterator(); iterator
				.hasNext();) {
			BillReminder billReminder = iterator.next();

			Calendar calCurDueDate = new GregorianCalendar();
			Calendar calDueDate = new GregorianCalendar();

			Date now = new Date();

			// Set current DB Due Date
			calCurDueDate.setTime(billReminder.getDueDate());

			// Set current date with the DB due date day of week
			calDueDate.setTime(now);
			calDueDate.set(Calendar.DAY_OF_MONTH,
					calCurDueDate.get(Calendar.DAY_OF_MONTH));

			calulateIfInRange(calDueDate, now, range, ret, billReminder);
		}

		return ret;
	}

	public List<BillReminder> findAll() {

		List<BillReminder> ret = new ArrayList<BillReminder>();
		BillReminder br = null;

		// look in cache first
		Cache cache = HomeFinanceRunner.getCache(CACHE_NAME);
		Map<Object, Element> elementMap = cache.getAll(cache
				.getKeysWithExpiryCheck());
		if (null == elementMap || elementMap.size() == 0) {
			Connection c = null;
			PreparedStatement p = null;
			ResultSet rs = null;

			try {
				c = HomeFinanceRunner.getConnection();

				p = c.prepareStatement("select id, name, amount, account, due_date, repeat"
						+ " from REMINDER");

				rs = p.executeQuery();

				while (rs.next()) {

					Integer dbId = rs.getInt(1);
					String repeatInterval = rs.getString(COLUMN_REPEAT);

					Date dbDueDate = rs.getDate(COLUMN_DUE_DATE);
					Calendar calCurDueDate = new GregorianCalendar();
					Calendar calDueDate = new GregorianCalendar();
					Date now = new Date();
					// Set current DB Due Date
					calCurDueDate.setTime(dbDueDate);

					// Set current date with the DB due date day of week
					calDueDate.setTime(now);
					calDueDate.set(Calendar.DAY_OF_MONTH,
							calCurDueDate.get(Calendar.DAY_OF_MONTH));

					br = new BillReminder(dbId, rs.getString(COLUMN_NAME),
							rs.getFloat(COLUMN_AMOUNT),
							new AccountDao().findByName(rs
									.getString(COLUMN_ACCOUNT)),
							getFutureDueDate(calDueDate.getTime()),
							repeatInterval);

					ret.add(br);
					cache.put(new Element(br.getName(), br));
				}
			} catch (SQLException e) {

				e.printStackTrace();
			} catch (IOException e) {
				e.printStackTrace();
			} finally {
				HomeFinanceRunner.closeConnection(c, p, rs);
			}
		} else {
			for (Iterator<Element> iterator = elementMap.values().iterator(); iterator
					.hasNext();) {

				Element element = iterator.next();

				br = (BillReminder) element.getObjectValue();
				ret.add(br);
			}
		}

		Collections.sort(ret);

		return ret;
	}

	private static Date getFutureDueDate(final Date inDate) {
		Date ret = inDate;
		Date now = new Date();

		if (inDate.before(now)) {
			Calendar cal = new GregorianCalendar();
			cal.setTime(inDate);
			cal.add(Calendar.MONTH, 1);
			ret = cal.getTime();
		}

		return ret;
	}

	private static void calulateIfInRange(final Calendar dueDateCal,
			final Date now, final Integer range, final List<BillReminder> list,
			final BillReminder br) {

		// If the due date is in the future
		if (dueDateCal.getTime().getTime() > now.getTime()) {

			// Find out if it is in range
			if (HomeFinanceRunner.daysBetween(now, dueDateCal.getTime()) <= range) {
				list.add(br);
			}

		} else {

			// Find how often the bill is due and move forward that amount of
			// time
			if ("Monthly".equals(br.getRepeatInterval())) {

				dueDateCal.add(Calendar.MONTH, 1);
				calulateIfInRange(dueDateCal, now, range, list, br);

			} else {
				System.out.println("NOT IMPLEMENTED!!!");
			}
		}
	}

	public Integer writeObject(final BillReminder billReminder) {
		Connection c = null;
		PreparedStatement p = null;
		ResultSet rs = null;
		Integer ret = null;

		try {
			c = HomeFinanceRunner.getConnection();

			p = c.prepareStatement("insert into REMINDER (name, amount, account, "
					+ "due_date, repeat) VALUES(?,?,?,?,?)");
			p.setString(HomeFinanceRunner.DB_COLUMN_FIRST,
					billReminder.getName());
			p.setFloat(HomeFinanceRunner.DB_COLUMN_SECOND,
					billReminder.getAmount());
			p.setString(HomeFinanceRunner.DB_COLUMN_THIRD, billReminder
					.getAccount().getName());
			p.setDate(HomeFinanceRunner.DB_COLUMN_FOURTH, new java.sql.Date(
					billReminder.getDueDate().getTime()));
			p.setString(HomeFinanceRunner.DB_COLUMN_FIFTH,
					billReminder.getRepeatInterval());
			p.executeUpdate();
			c.commit();

			p.close();

			// Get the generated ID
			p = c.prepareStatement("CALL IDENTITY()");
			rs = p.executeQuery();
			rs.next();
			int identity = rs.getInt(1);
			ret = identity;

			// update cache
			Cache cache = HomeFinanceRunner.getCache(CACHE_NAME);
			cache.removeAll();

		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			HomeFinanceRunner.closeConnection(c, p, rs);
		}

		return ret;
	}

	public final void delete(final BillReminder billReminder) {
		Connection c = null;
		PreparedStatement p = null;
		ResultSet rs = null;

		try {
			c = HomeFinanceRunner.getConnection();

			p = c.prepareStatement("delete from REMINDER where id = ?");
			p.setInt(1, billReminder.getId());

			p.executeUpdate();

			c.commit();

			// clear cache
			Cache cache = HomeFinanceRunner.getCache(CACHE_NAME);
			cache.removeAll();

		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			HomeFinanceRunner.closeConnection(c, p, rs);
		}
	}
}
